ALTER PROC sp_update_vendor_packing_slip_status 
  @flight_number varchar(50),
  @flight_route varchar(50),
  @etd varchar(50),
  @eta varchar(50),
  @hawb varchar(50),
  @mawb varchar(50),
  @shippingMethod varchar(100),
  @delivery_terms varchar(50),
  @no_of_boxes int,
  @psweight float,
  @tracking varchar(50),
  @palette_count int,
  @shipDate varchar(50),
  @shippingInstructions varchar(300),
  @comments varchar(300),
  @packed_by varchar(50),
  @title varchar(50),
  @freightInstructions varchar(300),
  @status varchar(20),
  @customer_shipping_id int,
  @INVOICE_NO VARCHAR(50),
  @gross_weight float,
  @uom_weight varchar(10),
  @package_dimension varchar(20),
  @uom_pkg_dimension varchar(10),
  @flight_no_two varchar(50)
  AS 
  BEGIN
  	declare @cslit_cursor CURSOR
  	declare @lot_cursor CURSOR
  	declare @cslit_id int
  	declare @product_type varchar(50)
  	declare @lot_id int
  	declare @qty_pull int
 	declare @drop_shipment int
 	declare @lot_date_in datetime
	declare @lot_entered_by bit
  	declare @il_qty_balance_existing int

	IF ( @status = 'SHIPPED' )
	 BEGIN
		DECLARE @DB_INV_NO VARCHAR(50)
		SELECT @DB_INV_NO = Inv_No FROM CUSTOMER_SHIPPING_TABLE WHERE CUSTOMERSHIPPING_ID = @CUSTOMER_SHIPPING_ID
		--AVOIDING UPDATING NEW INV NO IF INV NO ALREADY EXISTS IN DB
		--THIS IS DOING SINCE NEW INV NO IS UPDATING WHEN CLICKED CONFIRM BUTTON DOUBLE TIME CAUSING INV NO SEQUENCE MISSING
		IF ( @DB_INV_NO IS NOT NULL AND LEN(LTRIM(RTRIM(@DB_INV_NO))) > 0 )
		 BEGIN
			SET @INVOICE_NO = @DB_INV_NO
		 END
		UPDATE CUSTOMER_SHIPPING_TABLE SET CST_CONFIRMED_ON = CURRENT_TIMESTAMP WHERE CUSTOMERSHIPPING_ID = @CUSTOMER_SHIPPING_ID
	 END

  	UPDATE customer_shipping_table SET cst_status = @status,CST_Box_Count = @no_of_boxes,CST_AW_Bill_Number = @tracking, CST_Weight = @psweight, CST_Method = @shippingMethod, CST_Ship_Date = @shipDate,CST_Shipping_Instructions = @shippingInstructions,CST_comments = @comments, CST_Freight_Instructions = @freightInstructions,
		CST_FLIGHT_NUMBER = @flight_number, CST_FLIGHT_ROUTE = @flight_route, CST_ETD = @etd, CST_ETA = @eta, CST_HAWB = @hawb, CST_MAWB = @mawb, CST_FOB = @delivery_terms, CST_PALETTE_COUNT = @palette_count, CST_Packedby = @packed_by, CST_Title = @title, INV_NO = @INVOICE_NO, CST_Gross_Weight = @gross_weight,
		CST_UOM_Weight = @uom_weight, CST_Package_Dimension = @package_dimension, CST_UOM_Pkg_Dimension = @uom_pkg_dimension, CST_FLIGHT_NUMBER_TWO = @flight_no_two
		WHERE customershipping_id = @customer_shipping_id
	
  	UPDATE customer_shipping_line_item_table SET cslit_Status = @status WHERE customershipping_id = @customer_shipping_id
  	
  	SET @cslit_cursor = CURSOR FOR
  	SELECT cslit_id, isnull (p_type, 'CHIP') AS P_type FROM customer_shipping_line_item_table cslit, customer_open_order_lineItem cool, product WHERE cool.product_ID = product.product_ID and cslit.LineItem_ID = cool.LineItem_ID and  customershipping_id = @customer_shipping_id
  	-- select cslit_id from customer_shipping_line_item_table where customershipping_id = @customer_shipping_id
  	
  	OPEN @cslit_cursor
  		FETCH NEXT FROM @cslit_cursor INTO @cslit_id, @product_type
  		WHILE @@fetch_status = 0
  		BEGIN
  			UPDATE customer_shipping_lot_number_table SET cslnt_status = @status WHERE cslit_id = @cslit_id
  			FETCH NEXT FROM @cslit_cursor INTO @cslit_id, @product_type
  		END
  	CLOSE @cslit_cursor
  	DEALLOCATE @cslit_cursor
  END	
